/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ebdesk.controller.sppt;
import com.ebdesk.controller.dhkp.Lib;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import javax.swing.ImageIcon;

/**
 *
 * @author Ikmal Syifai
 */

public class SPPT {
    public Connection dbCon(){
        Connection c = null;
        
        try{
            Properties p = new Properties();
            FileInputStream in = new FileInputStream("config.properties");
            p.load(in);

            Class.forName(p.getProperty("driver"));   
            c = DriverManager.getConnection(p.getProperty("connectionstring"),p.getProperty("username"),p.getProperty("password"));
        }
        catch(FileNotFoundException e){
            System.err.println(e);
        }
        catch(IOException e){
            System.err.println(e);
        }
        catch(ClassNotFoundException e){
            System.err.println(e);
        }
        catch(SQLException e){
            System.err.println(e);
        }        
        finally{
            return c;
        }
    }
    
    public int[][] getCoordinate(){
        int[][] coord = null;
        
        try {
            Connection conn = dbCon();

            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            ResultSet rs;
            int row = 0;
            
            rs = s.executeQuery("select * from pbb.pbb_print_setting order by id");
            rs.last();
            int rowcount = rs.getRow();
            rs.beforeFirst();
            
            coord = new int[rowcount][2];                                            
            while(rs.next()){
                coord[row][0] = rs.getInt(2);
                coord[row][1] = rs.getInt(3);
                
                row++;
            }
            
            if(conn!=null){
                conn.close();
            }
            
            
        }
        catch (SQLException e) {
            System.err.println(e);
        }
        finally{
            return coord;
        }
    }
    
    public Object[][] getData(String query){
        Object[][] objData = null;
        
        try {
            Connection conn = dbCon();
            ImageIcon printer = new ImageIcon(getClass().getResource("/com/ebdesk/view/images/print.gif"));
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            ResultSet rs;
            int row = 0;
            int column = 0;
            int rowcount;
            int paging;
            
            //Cek pengaturan paging
            rs = s.executeQuery("select pbb.pbb_setting.value from pbb.pbb_setting where pbb.pbb_setting.setting = 'paging'");
            rs.next();
            paging = rs.getInt(1);
            
            //Hitung jumlah row dalam ResultSet
            rs = s.executeQuery(query);
            rs.last();
            rowcount = rs.getRow();
            rs.beforeFirst();
            
            if(rowcount>paging) rowcount = paging;

            objData= new Object[rowcount][11];                                            
            while(rs.next()){
                objData[row][column++] = Boolean.FALSE;//Pilih
                objData[row][column++] = Lib.FormatSPPT(rs.getString(1));//NOP
                objData[row][column++] = rs.getString(2);//Nama
                objData[row][column++] = rs.getString(3);//Alamat
                objData[row][column++] = rs.getString(26);//Tgl Terbit
                objData[row][column++] = rs.getString(27);//Tgl Cetak
                objData[row][column++] = rs.getString(28);//Tgl Jatuh Tempo
                objData[row][column++] = rs.getString(16);//Luas Bumi
                objData[row][column++] = rs.getString(17);//Luas Bangunan
                objData[row][column++] = Lib.NumberFormat(Double.parseDouble(rs.getString(29)));//Pajak Terhutang
                objData[row][column++] = printer;//Gambar Printer
                
                column = 0;
                row++;
            }
            
            if(conn!=null){
                conn.close();
            }                        
        }
        catch (SQLException e) {
            System.err.println(e);
        }        
        finally{
            return objData;
        }
    }
    
    public String[] getDetailData(String query){
        String [] objData = null;
        try {
            Connection conn = dbCon();
            
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            ResultSet rs;
            int column = 0;        
            
            objData = new String[29];
            
            rs = s.executeQuery(query);                            
            while(rs.next()){            
                objData[column++] = Lib.FormatSPPT(rs.getString(1));//NOP
                objData[column++] = rs.getString(3);//Alamat WP
                objData[column++] = rs.getString(8);//Kota/Kab
                objData[column++] = rs.getString(6);//Kelurahan
                objData[column++] = rs.getString(4);//RT
                objData[column++] = rs.getString(11);//Telepon
                objData[column++] = rs.getString(13);//Email
                objData[column++] = rs.getString(15);//NPWPD
                objData[column++] = rs.getString(16);//Luas Bumi
                objData[column++] = rs.getString(18);//Kelas Bumi
                objData[column++] = rs.getString(20);//NJOP Bumi
                objData[column++] = rs.getString(22);//NJOP
                objData[column++] = rs.getString(24);//NJKP
                objData[column++] = rs.getString(25);//Tahun Pajak            
                objData[column++] = rs.getString(27);//Tgl Cetak
                
                objData[column++] = rs.getString(2);//Nama
                objData[column++] = rs.getString(9);//Propinsi
                objData[column++] = rs.getString(7);//Kecamatan
                objData[column++] = rs.getString(10);//Kode Pos
                objData[column++] = rs.getString(5);//RW
                objData[column++] = rs.getString(12);//Handphone
                objData[column++] = rs.getString(14);//Fax
                objData[column++] = rs.getString(17);//Luas Bangunan
                objData[column++] = rs.getString(19);//Kelas Bangunan
                objData[column++] = rs.getString(21);//NJOP Bangunan
                objData[column++] = rs.getString(23);//NJOPTKP
                objData[column++] = Lib.NumberFormat(Double.parseDouble(rs.getString(29)));//Pajak Terhutang
                objData[column++] = rs.getString(26);//Tgl Terbit            
                objData[column++] = rs.getString(28);//Tgl Jatuh Tempo                                    
                
                column = 0;
            }
            
            if(conn!=null){
                conn.close();
            }                        
        }
        catch (SQLException e) {
            System.err.println(e);
        }    
        finally{
            return objData;
        }
    }
    
    public String[][] getPrintData(String query){
        String[][] objData = null;
        
        try {
            Connection conn = dbCon();
            
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            ResultSet rs;
            int row = 0;
            int column = 0;
            int rowcount;
            int paging;
            
            //Cek pengaturan paging
            rs = s.executeQuery("select pbb.pbb_setting.value from pbb.pbb_setting where pbb.pbb_setting.setting = 'paging'");
            rs.next();
            paging = rs.getInt(1);
            
            //Hitung jumlah row dalam ResultSet
            rs = s.executeQuery(query);
            rs.last();
            rowcount = rs.getRow();
            rs.beforeFirst();
            
            if(rowcount>paging) rowcount = paging;
            
            objData = new String[rowcount][38];                                
            while(rs.next()){
                objData[row][column++] = rs.getString(1);//0. NOP
                objData[row][column++] = rs.getString(47);//1. OP Alamat
                objData[row][column++] = rs.getString(48);//2. OP RT
                objData[row][column++] = rs.getString(49);//3. OP RW
                objData[row][column++] = rs.getString(50);//4. OP Kel
                objData[row][column++] = rs.getString(51);//5. OP Kec
                objData[row][column++] = rs.getString(52);//6. OP Kota
                objData[row][column++] = rs.getString(16);//7. OP Luas Bumi
                objData[row][column++] = rs.getString(18);//8. OP Kelas Bumi
                objData[row][column++] = rs.getString(20);//9. OP NJOP Bumi
                objData[row][column++] = rs.getString(17);//10. OP Luas Bangunan
                objData[row][column++] = rs.getString(19);//11. OP Kelas Bangunan
                objData[row][column++] = rs.getString(21);//12. OP NJOP Bangunan
                objData[row][column++] = rs.getString(22);//13. OP NJOP
                objData[row][column++] = rs.getString(23);//14. OP NJOPTKP
                objData[row][column++] = rs.getString(24);//15. OP NJKP
                objData[row][column++] = rs.getString(55);//16. OP Tarif
                objData[row][column++] = rs.getString(29);//17. PBB terhutang
                objData[row][column++] = rs.getString(30);//18. PBB pengurang
                objData[row][column++] = rs.getString(31);//19. PBB harus dibayar
                objData[row][column++] = rs.getString(28);//20. Tgl Jatuh Tempo
                objData[row][column++] = rs.getString(2);//21. WP Nama
                objData[row][column++] = rs.getString(3);//22. WP Alamat
                objData[row][column++] = rs.getString(4);//23. WP RT
                objData[row][column++] = rs.getString(5);//24. WP RW
                objData[row][column++] = rs.getString(6);//25. WP Kel
                objData[row][column++] = rs.getString(8);//26. WP Kota
                objData[row][column++] = rs.getString(25);//27. Tahun Pajak
                objData[row][column++] = rs.getString(58);//28. Nilai Per M2 Tanah
                objData[row][column++] = rs.getString(59);//29. Nilai Per M2 Bangunan
                objData[row][column++] = rs.getString(60);//30. Luas Bumi Bersama
                objData[row][column++] = rs.getString(61);//31. Luas Bangunan Bersama
                objData[row][column++] = rs.getString(62);//32. Kelas Bumi Bersama
                objData[row][column++] = rs.getString(63);//33. Kelas Bangunan Bersama
                objData[row][column++] = rs.getString(64);//34. Nilai Per M2 Tanah Bersama
                objData[row][column++] = rs.getString(65);//35. Nilai Per M2 Bangunan Bersama
                objData[row][column++] = rs.getString(66);//36. OP NJOP Bumi Bersama
                objData[row][column++] = rs.getString(67);//37. OP NJOP Bangunan Bersama

                column = 0;
                row++;
            }
            
            if(conn!=null){
                conn.close();
            }
            
            return objData;
        }
        catch (SQLException e) {
            System.err.println(e);
        }
        finally{
            return objData;
        }
    }
    
    public Object[] getPrinter(){
        Object[] dataPrinter = null;
        
        try {            
            int rowcount;
            Connection conn = dbCon();
            
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            ResultSet rs;
            
            rs = s.executeQuery("select * from pbb.pbb_sppt_printer");        
            
            rs.last();
            rowcount = rs.getRow();
            rs.beforeFirst();
            
            dataPrinter = new Object[rowcount+1];
            
            dataPrinter[0] = null;
            
            rowcount = 1;
            while(rs.next()){
                dataPrinter[rowcount] = "\\\\"+rs.getString(1)+"\\"+rs.getString(2);
                rowcount++;
            }
            
            if(conn!=null){
                conn.close();
            }                        
        }
        catch (SQLException e) {
            System.err.println(e);
        }        
        finally{
            return dataPrinter;
        }
    }         
     
    public Object[] getDataCB(int jeniscb){
        return getDataCB(jeniscb, "");
    }
    
    public Object[] getDataCB(int jeniscb, String kecamatan){
        Object[] dataCB = null;
        
        try {            
            String query = "";
            int rowcount;
            
            Connection conn = dbCon();
            
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            ResultSet rs;
            
            switch(jeniscb){
                //cbKec
                case 1: query = "select * "
                        + "from pbb.pbb_master_kecamatan "
                        + "where substring(pbb.pbb_master_kecamatan.kode_kecamatan from 1 for 4) = (select pbb.pbb_setting.value from pbb.pbb_setting where pbb.pbb_setting.setting = 'kode_kota') "
                        + "order by pbb.pbb_master_kecamatan.kode_kecamatan";
                        break;
                //cbKel
                case 2: query = "select * "
                        + "from pbb.pbb_master_kelurahan "
                        + "where substring(pbb.pbb_master_kelurahan.kode_kelurahan from 1 for 7) = (select pbb.pbb_master_kecamatan.kode_kecamatan from pbb.pbb_master_kecamatan where "
                        + "     pbb.pbb_master_kecamatan.nama_kecamatan = '"+kecamatan+"'"
                        + "     and substring(pbb.pbb_master_kecamatan.kode_kecamatan from 1 for 4) = (select pbb.pbb_setting.value from pbb.pbb_setting where pbb.pbb_setting.setting = 'kode_kota')) "
                        + "order by pbb.pbb_master_kelurahan.kode_kelurahan";
                        break;
                //cbBuku
                case 3: query = "select pbb.pbb_sppt_buku.nama_buku from pbb.pbb_sppt_buku order by pbb.pbb_sppt_buku.nama_buku";
                        break;
                            
            }
            
            rs = s.executeQuery(query);
            
            rs.last();
            rowcount = rs.getRow();
            rs.beforeFirst();        
            
            //Tambahkan string kosong ke combo box Kecamatan & Buku
            if(jeniscb!=2){
                dataCB = new Object[rowcount+1];            
                dataCB[0] = null;
                rowcount = 1;
            }else{
                dataCB = new Object[rowcount];            
                rowcount = 0;
            }
                    
            while(rs.next()){
                switch(jeniscb){
                    case 1: dataCB[rowcount] = rs.getString(4).substring(4) +" - "+rs.getString(3).toUpperCase(); break;
                    case 2: dataCB[rowcount] = rs.getString(4).substring(7) +" - "+rs.getString(3).toUpperCase(); break;
                    case 3: dataCB[rowcount] = rs.getString(1).toUpperCase(); break;
                }
                
                rowcount++;
            }
            
            if(conn!=null){
                conn.close();
            }            
        }
        catch (SQLException e) {
            System.err.println(e);
        }                
        finally{
            return dataCB;
        }
    }
    
    public Object getQuery(String query){
        Object dataQuery = null;
        try {
            Connection conn = dbCon();
            
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            ResultSet rs;        
            
            rs = s.executeQuery(query);                
            rs.next();
            dataQuery = rs.getObject(1);
            
            if(conn!=null){
                conn.close();
            }                        
        } 
        catch (SQLException e) {
            System.err.println(e);
        }                        
        finally{
            return dataQuery;
        }
    }
    
    public String[] getRowCol(String field){
        String[] rowcol = new String[3];
        
        try {                        
            Connection conn = dbCon();        
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            ResultSet rs;        
            
            rs = s.executeQuery("select * from pbb.pbb_print_setting where field ='"+field+"'");                
            rs.next();
            rowcol[0] = rs.getString(2);
            rowcol[1] = rs.getString(3);
            rowcol[2] = rs.getString(4);
            
            if(conn!=null){
                conn.close();
            }                        
        }
        catch (SQLException e) {
            System.err.println(e);
        }                    
        finally{
            return rowcol;
        }
    }
    
    public void setRowCol(Integer[] values, String field){
        try {
            Connection conn = dbCon();        
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);        
            s.executeUpdate("update pbb.pbb_print_setting set row = "+values[0].toString()+", col = "+values[1].toString()+" where field ='"+field+"'");                
            
            if(conn!=null){
                conn.close();
            }
        }
        catch (SQLException e) {
            System.err.println(e);
        }                            
    }
}
